package com.hnkywl.sts.dao.zjgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.zjgl.Pj;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * 说明：
 * 包:        com.hnkywl.sts.dao.sfgl
 * 类描述:    付款审核dao类
 * 修改备注: [说明本次修改内容]
 * 版本:        [v1.0]
 */
@Repository
public class PjglDao extends SimpleHibernateTemplate<Pj, Long> {
    /**
     * 查询票据管理
     * @param pjsflxYype
     * @param pjlxType
     * @param khztType
     * @param Ids
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Pj> findPjglList(String pjhm,Long sfkId,Integer  pjsflxYype,Integer pjlxType, Integer khztType, String gsMc,Long Ids,String khhMc,Date startDate,Date endDate,Integer zt,int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Pj> page = new Page<Pj>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (pjhm != null && !("").equals(pjhm)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjhm like ?");
            param.add("%"+pjhm.trim()+"%");
        }
        if (zt != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" status = ? ");
            param.add(zt);
        }
        if (sfkId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" sfk.id = ? ");
            param.add(sfkId);
        }
        if (pjsflxYype != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjsflx = ? ");
            param.add(pjsflxYype);
        }
        if (pjlxType != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjlx = ? ");
            param.add(pjlxType);
        }
        if (khztType != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" khzt = ? ");
            param.add(khztType);
        }
        if (Ids != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" nbgs.id = ? ");
            param.add(Ids);
        }
        if (Ids == null && gsMc!=null && !gsMc.equals("")) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" nbgs.jc like ? ");
            param.add("%" + gsMc.trim() + "%");
        }

        if (startDate != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" date_created >= ? ");
            param.add(startDate);
        }
        if (endDate != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" date_created < ? ");
            param.add(endDate);
        }
        if (pjsflxYype != null && pjsflxYype == 0) {
            if (khhMc != null && !("").equals(khhMc)) {
                if (condition.length() != 0)
                    condition.append(" and ");
                condition.append(" yscpyh like \'%"+khhMc.trim()+"%\' ");
            }
        }else if (pjsflxYype != null && pjsflxYype == 1) {
            if (khhMc!=null && !khhMc.equals("")) {
                if (condition.length() != 0)
                    condition.append(" and ");
                condition.append(" yfcpyh.qc like \'%"+khhMc.trim()+"%\' ");
            }
        }
        if (pjhm != null && !("").equals(pjhm)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjhm like ?");
            param.add("%"+pjhm.trim()+"%");
        }
        //==============记录数量===============
        hql = "select count(*) from Pj where 1=1  " + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition ;
//        hql = hql +" order by date_created desc";
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Pj where 1=1  "  + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
                hql = hql + " and " + condition ;
        hql = hql +" order by abs(day(dqr-current_timestamp())) ";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }



    /**
     * 查询未使用的票据
     * @return
     */
    public Page<Pj> findwsyPjglList(Long gsId,Integer  pjsflxYype,String khhMc,String pjhm,int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Pj> page = new Page<Pj>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (pjhm != null && !("").equals(pjhm)) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjhm like ?");
            param.add("%"+pjhm.trim()+"%");
        }
        if (pjsflxYype != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" pjsflx = ? ");
            param.add(pjsflxYype);
        }
        if (pjsflxYype != null && pjsflxYype == 0) {
            if (khhMc != null && !("").equals(khhMc)) {
                if (condition.length() != 0)
                    condition.append(" and ");
                condition.append(" yscpyh like \'%"+khhMc.trim()+"%\' ");
            }
        }else if (pjsflxYype != null && pjsflxYype == 1) {
            if (khhMc!=null && !khhMc.equals("")) {
                if (condition.length() != 0)
                    condition.append(" and ");
                condition.append(" yfcpyh.qc like \'%"+khhMc.trim()+"%\' ");
            }
        }
        if (gsId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" nbgs.id = ? ");
            param.add(gsId);
        }
        //==============记录数量===============
        hql = "select count(*) from Pj where status=1";
        if (condition.length() != 0)
                    hql = hql + " and " + condition ;
        hql = hql +" order by pjhm,dqr desc";
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Pj where status=1" ;
        if (condition.length() != 0)
                hql = hql + " and " + condition ;
        hql = hql +" order by pjhm,dqr desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    /**
     * 查询未使用的票据
     * @return
     */
    public Page<Pj>  findPjdhmx(Long  pjId,int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Pj> page = new Page<Pj>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (pjId != null) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" dhpj.id = ? ");
            param.add(pjId);
        }
        //==============记录数量===============
        hql = "select count(*) from Pj where status !=5 " + RegionDao.createDataRegion("and");
        if (condition.length() != 0){
            hql = hql + " and " + condition +" order by dqr ";
        }else{
            hql = hql +" order by dqr ";
        }
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Pj where status !=5"  + RegionDao.createDataRegion("and");
        if (condition.length() != 0){
            hql = hql + " and " + condition +" order by dqr ";
        }else{
            hql = hql +" order by dqr ";
        }
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    /**
     * 判断属性是否重复
     * @param id 付款审核id
     * @param propertyName 付款审核属性名
     * @param propertyValue 付款审核属性值
     * @return
     */
    public Boolean checkUnique(Long id,Integer pjsflxType, String propertyName,Object propertyValue) {
        String hql;
        if (propertyValue == null)
            return false;
        if(id == null){
            //修改检验
            hql = "from Pj where 1=1 and pjsflx = ? and " + propertyValue + " = ? ";
            return this.existsQuery(hql,pjsflxType, propertyName);
        }else{
            //新增检验
            hql = "from Pj where 1 =1 and pjsflx = ? and " + propertyValue + " = ? and id <>?";
            return this.existsQuery(hql,pjsflxType, propertyName,id);
        }
    }

    /**
     * 得到应付票据
     *
     * @param id
     * @return
     */
    public Pj getYFPjglDetail(Long id){
      return (Pj)find("from from Pj where pjsflx =1 and id = ?",id);
    }

    public List<Pj> getPjBySkId(Long skId){
        return (List<Pj>)find(" from Pj where SK_ID =  ? and deleted = 0  ",skId);
    }

    public List<Pj> getPjByFkId(Long fkId){
        return (List<Pj>)find(" from Pj where SFK_ID =  ? and deleted = 0  ",fkId);
    }

    public List<Pj> getPjByFkOrSkId(Long sfkId){
        return (List<Pj>)find(" from Pj where deleted = 0 and ( SFK_ID =  ? or SK_ID = ?)    ",sfkId,sfkId);
    }

    public List<Map> findSumYspj(String pjhm,Integer  pjsflxYype,Integer pjlxType, Long gsId,String khhMc,Date startDate,Date endDate,Integer zt) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sql = " select sum(t.dqje) as \"dqje\",sum(t.yslx) as \"yslx\" from t_biz_pj t " +
                " left join t_sys_gsjg g on g.id = t.nbgs_id";

        if (StringUtils.isNotEmpty(pjhm)) {
            condition.add(" t.pjhm like:pjhm ");
            param.put("pjhm", "%" + pjhm + "%");
        }

        if (pjsflxYype != null) {
            condition.add(" t.pjsflx=:pjsflx ");
            param.put("pjsflx", pjsflxYype);
        }

        if (pjlxType != null) {
            condition.add(" t.pjlx=:pjlx ");
            param.put("pjlx", pjlxType);
        }

        if (gsId != null) {
            condition.add(" g.id=:gs_id ");
            param.put("gs_id", gsId);
        }

        if (StringUtils.isNotEmpty(khhMc)) {
            condition.add(" t.yscpyh like:yscpyh ");
            param.put("yscpyh", "%" + khhMc + "%");
        }

        if (startDate != null) {
            condition.add(" t.date_created>= :startDate ");
            param.put("startDate", startDate);
        }

        if (endDate != null) {
            condition.add(" t.date_created<= :endDate ");
            param.put("endDate", endDate);
        }

        if(zt != null){
            condition.add(" t.status=:zt ");
            param.put("zt", zt);
        }

        String dataRegion = RegionDao.createSQLDataRegion(null, "t");
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql += " where t.deleted !=1 and " + whereSql + dataRegion;

        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    public List<Map> findSumYfpj(String pjhm,Integer  pjsflxYype,Integer pjlxType, Long gsId,Long khhId,Date startDate,Date endDate,Integer zt) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sql = " select sum(t.dqje) as \"dqje\",sum(t.yslx) as \"yslx\",sum(t.bzj) as \"bzj\" from t_biz_pj t " +
                " left join t_sys_gsjg g on g.id = t.nbgs_id" +
                " left join t_sys_khh k on k.id = t.yfcpyh_id";

        if (StringUtils.isNotEmpty(pjhm)) {
            condition.add(" t.pjhm like:pjhm ");
            param.put("pjhm", "%" + pjhm + "%");
        }

        if (pjsflxYype != null) {
            condition.add(" t.pjsflx=:pjsflx ");
            param.put("pjsflx", pjsflxYype);
        }

        if (pjlxType != null) {
            condition.add(" t.pjlx=:pjlx ");
            param.put("pjlx", pjlxType);
        }

        if (gsId != null) {
            condition.add(" g.id=:gs_id ");
            param.put("gs_id", gsId);
        }

        if (khhId != null) {
            condition.add(" k.id=:khhId ");
            param.put("khhId", khhId);
        }

        if (startDate != null) {
            condition.add(" t.date_created>= :startDate ");
            param.put("startDate", startDate);
        }

        if (endDate != null) {
            condition.add(" t.date_created<= :endDate ");
            param.put("endDate", endDate);
        }

        if(zt != null){
            condition.add(" t.status=:zt ");
            param.put("zt", zt);
        }

        String dataRegion = RegionDao.createSQLDataRegion(null, "t");
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ") + " and ";

        sql += " where t.deleted !=1 and " + whereSql + dataRegion;

        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    public List<Pj> checkYspjhm(Long gsId,String pjhm){
        String hql = "from Pj where pjsflx = 0 and nbgs.id = ? and pjhm = ? and deleted = 0  ";
        return find(hql,gsId,pjhm);
    }

}
